using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;

using UMEVOR.DTO;

using Microsoft.Practices.EnterpriseLibrary.Data;

namespace UMEVOR.DAL
{
    public class DALT_ARTICULISTA : DALConnection
    {

        #region Incluir

        public void incluir(T_ARTICULISTA obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" INSERT INTO T_ARTICULISTA ");
                SQL.Append("    (TX_NOME_ARTICULISTA, TX_EMAIL_CTTO, TX_URL_FOTO) ");
                SQL.Append(" VALUES ");
                SQL.Append("    ('" + obj.TX_NOME_ARTICULISTA + "', '" + obj.TX_EMAIL_CTTO + "', '" + obj.TX_URL_FOTO.Replace("\\","#") + "') ");

                DbCommand insertT_ARTICULISTA = database.GetSqlStringCommand(SQL.ToString());

                insertT_ARTICULISTA.CommandText = SQL.ToString();

                database.ExecuteNonQuery(insertT_ARTICULISTA, DALConnection.transaction);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Incluir[]

        public void incluir(T_ARTICULISTA[] obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();
                DbCommand[] insertT_ARTICULISTA = new DbCommand[obj.Length];

                for (int index = 0; index < obj.Length; index++)
                {
                    T_ARTICULISTA o = obj[index];
                    SQL = new StringBuilder();

                    SQL.Append(" INSERT INTO T_ARTICULISTA ");
                    SQL.Append("    (ID_ARTICULISTA, TX_NOME_ARTICULISTA, TX_EMAIL_CTTO, TX_URL_FOTO) ");
                    SQL.Append(" VALUES ");
                    SQL.Append("    (:ID_ARTICULISTA, :TX_NOME_ARTICULISTA, :TX_EMAIL_CTTO, :TX_URL_FOTO) ");

                    insertT_ARTICULISTA[index] = database.GetSqlStringCommand(SQL.ToString());

                    database.AddInParameter(insertT_ARTICULISTA[index], ":ID_ARTICULISTA", DbType.Int64, o.ID_ARTICULISTA);
                    database.AddInParameter(insertT_ARTICULISTA[index], ":TX_NOME_ARTICULISTA", DbType.String, o.TX_NOME_ARTICULISTA);
                    database.AddInParameter(insertT_ARTICULISTA[index], ":TX_EMAIL_CTTO", DbType.String, o.TX_EMAIL_CTTO);
                    database.AddInParameter(insertT_ARTICULISTA[index], ":TX_URL_FOTO", DbType.String, o.TX_URL_FOTO);

                    insertT_ARTICULISTA[index].CommandText = SQL.ToString();
                }

                try
                {
                    foreach (DbCommand cmd in insertT_ARTICULISTA)
                    {
                        database.ExecuteNonQuery(cmd, DALConnection.transaction);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Alterar

        public void alterar(T_ARTICULISTA obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" UPDATE T_ARTICULISTA SET ");
                SQL.Append("    TX_NOME_ARTICULISTA = '" + obj.TX_NOME_ARTICULISTA + "' , ");
                SQL.Append("    TX_EMAIL_CTTO = '" + obj.TX_EMAIL_CTTO + "' , ");
                SQL.Append("    TX_URL_FOTO = '" + obj.TX_URL_FOTO.Replace("\\", "#") + "' ");
                SQL.Append(" WHERE ");
                SQL.Append("     ID_ARTICULISTA = " + obj.ID_ARTICULISTA.ToString());

                DbCommand updateT_ARTICULISTA = database.GetSqlStringCommand(SQL.ToString());
                updateT_ARTICULISTA.CommandText = SQL.ToString();

                database.ExecuteNonQuery(updateT_ARTICULISTA, DALConnection.transaction);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Alterar[]

        public void alterar(T_ARTICULISTA[] obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();
                DbCommand[] updateT_ARTICULISTA = new DbCommand[obj.Length];

                for (int index = 0; index < obj.Length; index++)
                {
                    T_ARTICULISTA o = obj[index];
                    SQL = new StringBuilder();
                    SQL.Append(" UPDATE T_ARTICULISTA SET ");
                    SQL.Append("    TX_NOME_ARTICULISTA = :TX_NOME_ARTICULISTA , ");
                    SQL.Append("    TX_EMAIL_CTTO = :TX_EMAIL_CTTO , ");
                    SQL.Append("    TX_URL_FOTO = :TX_URL_FOTO ");
                    SQL.Append(" WHERE ");
                    SQL.Append("     ID_ARTICULISTA = :ID_ARTICULISTA  ");

                    updateT_ARTICULISTA[index] = database.GetSqlStringCommand(SQL.ToString());

                    database.AddInParameter(updateT_ARTICULISTA[index], ":ID_ARTICULISTA", DbType.Int64, o.ID_ARTICULISTA);
                    database.AddInParameter(updateT_ARTICULISTA[index], ":TX_NOME_ARTICULISTA", DbType.String, o.TX_NOME_ARTICULISTA);
                    database.AddInParameter(updateT_ARTICULISTA[index], ":TX_EMAIL_CTTO", DbType.String, o.TX_EMAIL_CTTO);
                    database.AddInParameter(updateT_ARTICULISTA[index], ":TX_URL_FOTO", DbType.String, o.TX_URL_FOTO);

                    updateT_ARTICULISTA[index].CommandText = SQL.ToString();
                }

                try
                {
                    foreach (DbCommand cmd in updateT_ARTICULISTA)
                    {
                        database.ExecuteNonQuery(cmd, DALConnection.transaction);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Excluir

        public void excluir(T_ARTICULISTA obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" DELETE FROM T_ARTICULISTA ");
                SQL.Append(" WHERE ");
                SQL.Append("     ID_ARTICULISTA = " + obj.ID_ARTICULISTA.ToString());

                DbCommand deleteT_ARTICULISTA = database.GetSqlStringCommand(SQL.ToString());

                deleteT_ARTICULISTA.CommandText = SQL.ToString();

                database.ExecuteNonQuery(deleteT_ARTICULISTA, DALConnection.transaction);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Excluir[]

        public void excluir(T_ARTICULISTA[] obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();
                DbCommand[] deleteT_ARTICULISTA = new DbCommand[obj.Length];

                for (int index = 0; index < obj.Length; index++)
                {
                    T_ARTICULISTA o = obj[index];
                    SQL = new StringBuilder();
                    SQL.Append(" DELETE FROM T_ARTICULISTA ");
                    SQL.Append(" WHERE ");
                    SQL.Append("     ID_ARTICULISTA = :ID_ARTICULISTA  ");

                    deleteT_ARTICULISTA[index] = database.GetSqlStringCommand(SQL.ToString());

                    database.AddInParameter(deleteT_ARTICULISTA[index], ":ID_ARTICULISTA", DbType.Int64, o.ID_ARTICULISTA);

                    deleteT_ARTICULISTA[index].CommandText = SQL.ToString();
                }

                try
                {
                    foreach (DbCommand cmd in deleteT_ARTICULISTA)
                    {
                        database.ExecuteNonQuery(cmd, DALConnection.transaction);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region ObterPorPK

        public T_ARTICULISTA obterPorPK(long pID_ARTICULISTA)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" SELECT * FROM T_ARTICULISTA ");
                SQL.Append(" WHERE ");
                SQL.Append("  ID_ARTICULISTA = " + pID_ARTICULISTA.ToString() + " ORDER BY TX_NOME_ARTICULISTA");

                DbCommand consultaT_ARTICULISTA = database.GetSqlStringCommand(SQL.ToString());
                consultaT_ARTICULISTA.CommandText = SQL.ToString();

                IDataReader reader = database.ExecuteReader(consultaT_ARTICULISTA, DALConnection.transaction);
                T_ARTICULISTA obj = null;

                if (reader.Read())
                {
                    obj = new T_ARTICULISTA();
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_ARTICULISTA")))
                    {
                        obj.ID_ARTICULISTA = Int64.Parse(reader["ID_ARTICULISTA"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_NOME_ARTICULISTA")))
                    {
                        obj.TX_NOME_ARTICULISTA = reader["TX_NOME_ARTICULISTA"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_EMAIL_CTTO")))
                    {
                        obj.TX_EMAIL_CTTO = reader["TX_EMAIL_CTTO"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_URL_FOTO")))
                    {
                        obj.TX_URL_FOTO = reader["TX_URL_FOTO"].ToString().Replace("#","\\");
                    }
                }
                reader.Dispose();
                consultaT_ARTICULISTA.Dispose();

                return obj;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region ListarTodos

        public List<T_ARTICULISTA> listarTodos()
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" SELECT * FROM T_ARTICULISTA ORDER BY TX_NOME_ARTICULISTA");

                DbCommand consultaT_ARTICULISTA = database.GetSqlStringCommand(SQL.ToString());
                consultaT_ARTICULISTA.CommandText = SQL.ToString();

                IDataReader reader = database.ExecuteReader(consultaT_ARTICULISTA, DALConnection.transaction);
                List<T_ARTICULISTA> lista = new List<T_ARTICULISTA>();
                T_ARTICULISTA obj = null;

                while (reader.Read())
                {
                    obj = new T_ARTICULISTA();
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_ARTICULISTA")))
                    {
                        obj.ID_ARTICULISTA = Int64.Parse(reader["ID_ARTICULISTA"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_NOME_ARTICULISTA")))
                    {
                        obj.TX_NOME_ARTICULISTA = reader["TX_NOME_ARTICULISTA"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_EMAIL_CTTO")))
                    {
                        obj.TX_EMAIL_CTTO = reader["TX_EMAIL_CTTO"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_URL_FOTO")))
                    {
                        obj.TX_URL_FOTO = reader["TX_URL_FOTO"].ToString().Replace("#", "\\");
                    }
                    lista.Add(obj);
                }
                reader.Dispose();
                consultaT_ARTICULISTA.Dispose();

                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public List<T_ARTICULISTA> consultar(string descricao)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" SELECT * FROM T_ARTICULISTA");
                SQL.Append(" WHERE TX_NOME_ARTICULISTA LIKE '" + descricao + "%' ORDER BY TX_NOME_ARTICULISTA");

                DbCommand consultaT_ARTICULISTA = database.GetSqlStringCommand(SQL.ToString());
                consultaT_ARTICULISTA.CommandText = SQL.ToString();

                IDataReader reader = database.ExecuteReader(consultaT_ARTICULISTA, DALConnection.transaction);
                List<T_ARTICULISTA> lista = new List<T_ARTICULISTA>();
                T_ARTICULISTA obj = null;

                while (reader.Read())
                {
                    obj = new T_ARTICULISTA();
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_ARTICULISTA")))
                    {
                        obj.ID_ARTICULISTA = Int64.Parse(reader["ID_ARTICULISTA"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_NOME_ARTICULISTA")))
                    {
                        obj.TX_NOME_ARTICULISTA = reader["TX_NOME_ARTICULISTA"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_EMAIL_CTTO")))
                    {
                        obj.TX_EMAIL_CTTO = reader["TX_EMAIL_CTTO"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_URL_FOTO")))
                    {
                        obj.TX_URL_FOTO = reader["TX_URL_FOTO"].ToString().Replace("#", "\\");
                    }
                    lista.Add(obj);

                }
                reader.Dispose();
                consultaT_ARTICULISTA.Dispose();

                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

    }
}

